package com.softer.chengxin.home.dao;

import com.softer.chengxin.common.domin.Module;
import com.softer.chengxin.common.domin.Page;
import com.softer.chengxin.corp.domin.Corp;
import com.softer.chengxin.home.dao.provider.HomeDaoProvider;
import com.softer.chengxin.home.domin.Column;
import com.softer.chengxin.home.domin.Pie;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author Gong Zhiyang
 * @date 2018/9/26
 */
@Mapper
@Repository
public interface HomeDao {

    /**
     * 根据用户名查询diccode
     * @param loginName
     * @return
     */
    @Select("SELECT dic_code FROM roules r LEFT JOIN manager_user m on r.id = m.role_id WHERE m.login_name = #{name}")
    String getDicCodeByLoginName(@Param("name") String loginName);
    /**
     * 获取物业企业菜单
     *
     * @param id 角色ID
     * @return 权限
     */
    @Select("SELECT mo_dules FROM roules WHERE id = #{id}")
    String selectModulesIdById(String id);

    /**
     * 根据登陆用户获取权限
     *
     * @param userName 登陆用户
     * @return 权限
     */
    @Select("SELECT b.mo_dules FROM manager_user AS a,roules AS b WHERE a.role_id=b.id  AND a.login_name=#{userName}")
    String selectModulesId(String userName);

    /**
     * 获取权限列表
     *
     * @param modulesId 权限id
     * @return 权限列表
     */
    @Select("select id AS Id, module_name AS  ModuleName , parent_id AS ParentId, url AS Url, icon AS Icon, sort AS Sort" +
            " from module WHERE id in (${id})")
    List<Module> selectModules(@Param("id") String modulesId);

//    /**
//     * admin登陆时，查询待审核企业信息
//     *
//     * @param id    admin主管的区域Id
//     * @param state 状态为1的是待审核
//     * @return 数量
//     */
//    @Select("select COUNT(*) from company_base_info where street NOT IN(${ids}) and state=#{state}")
//    int countByStateNotInDistrinct(@Param("ids") String id, @Param("state") int state);

    /**
     * 查找企业信息待审批
     *
     * @param id    主管的区域Id
     * @param state 状态为1的是待审核
     * @return 未审核消息条数
     */
    @SelectProvider(type = HomeDaoProvider.class, method = "findCountByState")
    int findCountByState(@Param("ids") String id, @Param("state") String state, @Param("provinceCompany") String provinceCompany);

    /**
     * 查询项目待办事项数量
     *
     * @param id        地区集合
     * @param info_type 信息种类
     * @param state     状态
     * @return 条数
     */
    @Select("select COUNT(*) from score_detail a left join company_project b on  a.project_id=b.id where a.project_id<>0 and b.street IN(${ids})  and a.info_type=#{info_type} and a.state=#{state}")
    int findCountByStateAndInfoType(@Param("ids") String id, @Param("info_type") int info_type, @Param("state") int state);

    /**
     * 查询企业待办事项数量
     *
     * @param id        地区集合
     * @param info_type 信息种类
     * @param state     状态
     * @return 数量
     */
    @Select("select COUNT(*) from score_detail a left join company_base_info b on  a.corp_id=b.id where a.project_id=0 and b.street IN(${ids}) and a.info_type=#{info_type} and a.state=#{state}")
    int findCorpDbsxNum(@Param("ids") String id, @Param("info_type") int info_type, @Param("state") int state);

    /**
     * 查询项目待办事项数量
     *
     * @param id        地区集合
     * @param info_type 信息种类
     * @param state     状态
     * @param loginName 审核人
     * @return 条数
     */
    @Select("select COUNT(*) from score_detail a left join company_project b on  a.project_id=b.id where a.project_id<>0 and b.street IN(${ids})  and a.info_type=#{info_type} and a.state=#{state}")
    int findAppealCount(@Param("ids") String id, @Param("info_type") int info_type, @Param("state") int state, @Param("loginName") String loginName);

    /**
     * 查询企业待办事项数量
     *
     * @param id        地区集合
     * @param info_type 信息种类
     * @param state     状态
     * @param loginName 审核人
     * @return 数量
     */
    @Select("select COUNT(*) from score_detail a left join company_base_info b on  a.corp_id=b.id where a.project_id=0 and b.street IN(${ids}) and a.info_type=#{info_type} and a.state=#{state}")
    int findAppealNum(@Param("ids") String id, @Param("info_type") int info_type, @Param("state") int state, @Param("loginName") String loginName);

    /**
     * 查询项目信息待审查数量
     *
     * @param id    区域ID
     * @param state 状态
     * @return 数量
     */
    @Select("SELECT COUNT(*) FROM company_project WHERE street IN(${ids}) AND state IN (#{state})")
    int countPendingProjectNum(@Param("ids") String id, @Param("state")String state);

    /**
     * 查询教育待审批数量
     *
     * @param id         区域ID
     * @param department 主办部门
     * @return 数量
     */
    @Select("SELECT COUNT(*) from company_train a ,company_base_info b WHERE a.corp_id=b.id and b.street in (${ids}) and a.state =1 AND a.department in (${department})")
    int getJyCount(@Param("ids") String id, @Param("department") String department);

    /**
     * 通过id查询corpid
     *
     * @param id 账号ID
     * @return area 管理区域
     */
    @Select("select area from manager_user where id=#{id} ")
    String findAreaId(@Param("id") int id);

    /**
     * 查询登陆账号的角色ID
     *
     * @param id 账号ID
     * @return 角色ID
     */
    @Select("select role_id from manager_user where id=#{id} ")
    Integer getRoleId(@Param("id") int id);

    /**
     * 获取企业登陆名
     *
     * @param loginName 登陆账号
     * @return 用户名
     */
    @Select("SELECT `name` FROM company_base_info WHERE qy_no=#{loginName}")
    String getCorpName(@Param("loginName") String loginName);

    /**
     * 获取管理部门用户名
     *
     * @param loginName 登陆账号
     * @return 用户名
     */
    @Select("SELECT `name` FROM manager_user WHERE login_name=#{loginName}")
    String getManagerName(@Param("loginName") String loginName);

    /**
     * 获取柱状图
     * 市级或者协会
     *
     * @return 集合
     */
    @Select("SELECT a.`name`,IFNULL(b.qyNO, 0) AS companyNum,IFNULL(c.allArea, 0) AS allArea," +
            "IFNULL(c.proNmu, 0) AS projectNum FROM district_street a" +
            " LEFT JOIN (SELECT district,COUNT(qy_no) AS qyNO FROM company_base_info" +
            " WHERE state = 2 GROUP BY district ) b ON b.district = a.id" +
            " LEFT JOIN (SELECT district,COUNT(project_name) AS proNmu,SUM(with_elevator_area) AS allArea" +
            " FROM company_project WHERE state = 2 GROUP BY district)c ON c.district=a.id WHERE a.parent_id = 0")
    List<Column> getColumnList();

    /**
     * 获取柱状图
     * 区级或街道
     *
     * @param area 管理区域
     * @return 集合
     */
    @Select("SELECT c.`name`,COUNT(a.qy_no) AS companyNum,IFNULL(b.projectNum,0) as projectNum,IFNULL(b.allArea,0)as allArea FROM company_base_info a" +
            " LEFT JOIN (SELECT street,COUNT(project_name) AS projectNum,SUM(with_elevator_area) AS allArea" +
            " FROM company_project WHERE state = 2 GROUP BY street ) b ON b.street = a.street LEFT JOIN district_street c ON a.street=c.id" +
            " where a.state = 2 and a.street in (${area}) GROUP BY a.street")
    List<Column> getDistrictColumnList(@Param("area") String area);

    /**
     * 获取饼图
     * 市级或者协会
     *
     * @param year 年份
     * @return 集合
     */
    @Select("SELECT a.name ,IFNULL(b.`value`,0) as value FROM level_type a LEFT JOIN (SELECT IF(dj='-','未审核',dj) as name,COUNT(poguid) as value  FROM score_record WHERE `year`=#{year}  GROUP BY dj)b ON b.`name`=a.name")
    List<Pie> getPieList(@Param("year") String year);

    /**
     * 获取饼图
     * 区级或街道
     *
     * @param year 年份
     * @param area 管理区域
     * @return 集合
     */
    @Select("SELECT IF(a.dj='-','未审核',a.dj) as name,COUNT(a.poguid) as value  FROM score_record a LEFT JOIN company_base_info b ON a.poguid = b.id WHERE a.`year`=#{year} AND b.street in (${area}) GROUP BY a.dj")
    List<Pie> getDistrictPieList(@Param("year") String year, @Param("area") String area);

    /**
     * 查询在线企业数量
     *
     * @param area   管理区域
     * @param online 是否在线（0否，1是）
     * @return 数量
     */
    @SelectProvider(type = HomeDaoProvider.class, method = "getOnlineCorpNum")
    Integer getOnlineCorpNum(@Param("area") String area, @Param("online") String online);

    /**
     * 查询在线企业列表
     *
     * @param area   管理区域
     * @param page   页面信息
     * @param online 是否在线（0否，1是）
     * @return 企业列表
     */
    @SelectProvider(type = HomeDaoProvider.class, method = "getOnlineCorpList")
    List<Corp> getOnlineCorpList(@Param("area") String area, @Param("page") Page page, @Param("online") String online);

    /**
     * 查询企业总数
     *
     * @param area 管理区域
     * @return 数量
     */
    @Select("SELECT COUNT(*) FROM company_base_info where street in(${area})")
    int getQyNum(@Param("area") String area);
}
